﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Data.SqlClient;

namespace skydream
{
     public  class ExcelHelp
    {
         /// <summary>
         /// 通过数据源方式得到Excel的链接；
         /// </summary>
         /// <param name="excelFile"></param>
         /// <returns></returns>
         public static OleDbConnection getExcelConn(string excelFile)
         {
             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + 
                 excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                return conn;
         }
         /// <summary>
         /// 读取Excel文件得到该数据表的内容
         /// </summary>
         /// <param name="strconn"></param>
         /// <returns></returns>
         public static DataSet getExcelData(OleDbConnection strconn, string workSheet)
         {
             DataSet ds = null;
             strconn.Open();
             try
             {
                 string strExcel = "";
                 OleDbDataAdapter myCommand = null;
                 
                 strExcel = "select * from [" + workSheet + "]";
                 myCommand = new OleDbDataAdapter(strExcel, strconn);
                 ds = new DataSet();
                 myCommand.Fill(ds, "table1");
             }
             catch (Exception ee)
             {
                 System.Diagnostics.Debug.WriteLine("读取Excel工作表内容时发生错误：" + ee.Message);
             }
             finally
             {
                 strconn.Close();
             }
             return ds;
         }
         /// <summary>
         /// 读取Excel文件得到该Excel文件的数据工作表
         /// </summary>
         /// <param name="strconn"></param>
         /// <returns></returns>
         public static ArrayList getExcelSheets(OleDbConnection strconn, string workSheet)
         {
             strconn.Open();
             ArrayList lists = new ArrayList();
             try
             {
                 DataTable dtOle = strconn.GetSchema("Tables");
                 DataTableReader dtReader = new DataTableReader(dtOle);
                 //char[] ch = new char[1] { '$'};
                 while (dtReader.Read())
                 {
                     lists.Add(dtReader["Table_Name"].ToString());
                 }
                 dtReader = null;
                 dtOle = null;
             }
             catch (Exception ee)
             {
                 System.Diagnostics.Debug.WriteLine("读取Excel工作表时发生错误：" + ee.Message);
             }
             finally
             {
                 strconn.Close();
             }
             return lists;
         }
         /// <summary>
         /// 写文件！
         /// </summary>
         /// <param name="strconn"></param>
         public static void writtenExcelData(OleDbConnection strconn, SqlParameter[] parameters )
         {
             strconn.Open();
             try
             {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = strconn;
                //for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
                //{
                //    if (fp2.Sheets[0].Cells[i, 0].Text != "")
                //    {
                //        cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
                //         fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
                //         "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
                //        cmd.ExecuteNonQuery();
                //    }
                //}
             }
             catch (Exception ee)
             {
                 System.Diagnostics.Debug.WriteLine("写Excel工作表时发生错误：" + ee.Message);
             }
             finally
             {
                 strconn.Close();
             }
             //return lists;
         }
    }
}
